package com.md.util;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.md.base.exception.ServerException;
import com.md.model.bo.ExcelClassField;
import com.md.model.bo.ExcelExportInfo;
import com.md.model.bo.ExcelRow;
import com.md.value.enums.ErrorEnum;
import lombok.experimental.UtilityClass;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URL;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * @author 刘骄阳
 * 2022-10-31 22:26
 */
@UtilityClass
@Slf4j
public class ExcelUtils {
  private static final String XLSX = ".xlsx";
  private static final String XLS = ".xls";
  private static final int IMG_HEIGHT = 30;
  private static final int IMG_WIDTH = 30;
  private static final int BYTES_DEFAULT_LENGTH = 10240;

  public static List<Map<String, String>> readFileToMap(final File file) throws IOException {
    return getBeanMap(readFile(file));
  }

  public static <T> List<T> readFile(final File file, final Class<T> clazz) throws IOException {
    return getBeanList(readFile(file), clazz);
  }

  public static <T> List<T> readUrl(final String url, final Class<T> clazz) throws IOException {
    return getBeanList(readExcel(url), clazz);
  }

  public static <T> List<T> readMultipartFile(final MultipartFile mFile, final Class<T> clazz) throws IOException {
    return getBeanList(readMultipartFile(mFile), clazz);
  }

  public static List<ExcelRow> readFile(final File file) throws IOException {
    return readExcel(null, file);
  }

  public static List<ExcelRow> readMultipartFile(final MultipartFile mFile) throws IOException {
    return readExcel(mFile, null);
  }

  public static Map<String, List<ExcelRow>> readFileManySheet(final File file) throws IOException {
    return readExcelManySheet(null, file);
  }

  public static Map<String, List<ExcelRow>> readFileManySheet(final MultipartFile file) throws IOException {
    return readExcelManySheet(file, null);
  }

  /**
   * 将数据转存到对象中
   *
   * @param array 数据
   * @param clazz 类型
   * @param <T>   泛型
   * @return 对象集合
   */
  private static <T> List<T> getBeanList(final List<ExcelRow> array, final Class<T> clazz) {
    return CollectionUtil.map(array, excelRow -> {
      try {
        return getBean(clazz, excelRow.getCellMap());
      } catch (ReflectiveOperationException e) {
        log.error("读取excel文件失败:  {}", e.getMessage(), e);
        throw ServerException.build(ErrorEnum.READ_FILE_ERROR);
      }
    });
  }

  /**
   * 将数据转存到对象中
   *
   * @param array 数据
   * @return 对象集合
   */
  private static List<Map<String, String>> getBeanMap(final List<ExcelRow> array) {
    return CollectionUtil.map(array, ExcelRow::getCellMap);
  }

  /**
   * 获取每个对象的数据
   */
  private static <T> T getBean(final Class<T> c,
                               final Map<String, String> cellMap) throws ReflectiveOperationException {
    // 生成对象
    T t = c.getDeclaredConstructor().newInstance();
    // 不能获取父级,需要获取父级的属性
    // 获取属性
    Field[] fields = c.getDeclaredFields();
    for (Field field : fields) {
      // 设置对应属性值
      setFieldValue(t, field, cellMap);
    }
    return t;
  }

  /**
   * 设置属性值
   *
   * @param t       对象
   * @param field   属性
   * @param cellMap 属性名与值
   * @param <T>     泛型
   */
  private static <T> void setFieldValue(final T t, final Field field,
                                        final Map<String, String> cellMap) throws ReflectiveOperationException {
    // 获取 ExcelImport 注解属性
//    ExcelImport annotation = field.getAnnotation(ExcelImport.class);
//    // 获取具体值
//    // 获取属性名
//    String fieldName = field.getName();
//    boolean require = false;
//    if (ObjectUtil.notEmpty(annotation)) {
//      // 获取是否必填
//      require = annotation.required();
//      // 获取注解上配置的导入名
//      String key = annotation.value();
//      // 如果包含直接获取
//      if (cellMap.containsKey(key)) {
//        // 获取值
//        fieldName = key;
//      }
//    }
//    // 判断是否必填
//    if (require && ObjectUtil.isEmpty(StringUtil.getVal(cellMap.get(fieldName)))) {
//      // 说明必填,但是值是空的抛出异常
//      throw ServerException.build(ErrorEnum.READ_FILE_REQUIRED_ERROR, fieldName);
//    }
//    // 其余情况根据类型赋值
//    ReflectionUtils.makeAccessible(field);
//    ReflectionUtils.setField(field, t, ClassUtils.judgeClassType(field::getType, cellMap.get(fieldName)));
  }

  private static Map<String, List<ExcelRow>> readExcelManySheet(final MultipartFile mFile, final File file)
    throws IOException {
    Workbook book = getWorkbook(mFile, file, null);
    if (book == null) {
      return Maps.newHashMap();
    }
    Map<String, List<ExcelRow>> map = new LinkedHashMap<>();
    try {
      int index = book.getNumberOfSheets();
      for (int i = 0; i < index; i++) {
        Sheet sheet = book.getSheetAt(i);
        map.put(sheet.getSheetName(), readSheet(sheet));
      }
    } finally {
      book.close();
    }
    return map;
  }

  private static List<ExcelRow> readExcel(final MultipartFile mFile, final File file) throws IOException {
    Workbook book = getWorkbook(mFile, file, null);
    if (book == null) {
      return Lists.newArrayList();
    }
    try {
      return readSheet(book.getSheetAt(0));
    } finally {
      book.close();
    }
  }

  private static List<ExcelRow> readExcel(final String url) throws IOException {
    Workbook book = getWorkbook(null, null, url);
    if (book == null) {
      return Lists.newArrayList();
    }
    try {
      return readSheet(book.getSheetAt(0));
    } finally {
      book.close();
    }
  }

  private static List<ExcelRow> readExcel(final MultipartFile mFile, final File file,
                                          final Integer sheetIndex) throws IOException {
    Workbook book = getWorkbook(mFile, file, null);
    if (book == null) {
      return Lists.newArrayList();
    }
    try {
      return readSheet(book.getSheetAt(sheetIndex));
    } finally {
      book.close();
    }
  }

  /**
   * 读取文件,转成WordBook
   *
   * @param multipartFile 文件
   * @param file          文件
   * @return 工作簿
   */
  private static Workbook getWorkbook(final MultipartFile multipartFile,
                                      final File file,
                                      final String url) throws IOException {
//    if (ObjectUtil.isEmpty(multipartFile) && !FileUtil.judgeFileExists(file) && !MatchUtil.isNormalURL(url)) {
//      return null;
//    }
//    // 解析表格数据
//    Workbook book = null;
//    try (FileStream fileStream = FileStream.of(multipartFile, file, url)) {
//      if (fileStream.getFileName().endsWith(XLSX)) {
//        book = new XSSFWorkbook(fileStream.getInputStream());
//      } else if (fileStream.getFileName().endsWith(XLS)) {
//        book = new HSSFWorkbook(new POIFSFileSystem(fileStream.getInputStream()));
//      } else {
//        return null;
//      }
//    } finally {
//      if (book != null) {
//        book.close();
//      }
//    }
//    return book;
    return null;
  }

  /**
   * 读取工作簿
   *
   * @param sheet 工作簿
   * @return 工作簿转成List
   */
  private static List<ExcelRow> readSheet(final Sheet sheet) {
    List<ExcelRow> list = Lists.newArrayList();

    // 读取首行
    int rowStart = sheet.getFirstRowNum();
    // 读取尾行
    int rowEnd = sheet.getLastRowNum();
    // 读取第一行,一般第一行都是表头
    Row headRow = sheet.getRow(rowStart);
    if (headRow == null) {
      return list;
    }
    // 读取起始列的下标
    int startCell = headRow.getFirstCellNum();
    int endCell = headRow.getLastCellNum();
    Map<Integer, String> keyMap = new HashMap<>();
    for (int j = startCell; j < endCell; j++) {
      // 获取表头数据
      String value = getCellValue(headRow.getCell(j));
      if (ObjectUtil.notEmpty(value)) {
        keyMap.put(j, value);
      }
    }
    // 如果表头没有数据则不进行解析
    if (ObjectUtil.isEmpty(keyMap)) {
      return list;
    }
    for (int i = rowStart + 1; i <= rowEnd; i++) {
      Row row = sheet.getRow(i);
      if (row == null) {
        continue;
      }
      Map<String, String> cellMap = Maps.newHashMap();
      for (int j = startCell; j < endCell; j++) {
        cellMap.put(keyMap.get(j), getCellValue(row.getCell(j)));
      }
      ExcelRow excelRow = new ExcelRow();
      excelRow.setRowIndex(i + 1);
      excelRow.setDistinctValue(String.join("", cellMap.values()));
      excelRow.setCellMap(cellMap);
      // 过滤掉空数据
      if (excelRow.getDistinctValue().length() > 0) {
        list.add(excelRow);
      }
    }
    return list;
  }

  /**
   * 读取cell
   *
   * @param cell 列
   * @return 字符串
   */
  private static String getCellValue(final Cell cell) {
    if (ObjectUtil.isEmpty(cell)) {
      return "";
    }
    // 空白或空
    CellType cellType = cell.getCellType();
    switch (cellType) {
      case NUMERIC:
        // 数值
        if (DateUtil.isCellDateFormatted(cell)) {
          //判断是否为日期类型
          return DateUtils.format(cell.getLocalDateTimeCellValue());
        } else {
          // 否则当数字转换
          DecimalFormat df = new DecimalFormat("####.####");
          return df.format(cell.getNumericCellValue());
        }
      case STRING:
        // 字符串
        return getStringValue(cell, true);
      case BLANK:
        // 空值
        return null;
      case BOOLEAN:
        // 布尔值
        return String.valueOf(cell.getBooleanCellValue());
      case _NONE:
      case ERROR:
        // 错误
        break;
      case FORMULA:
        // 公式型
        break;
      default:
        break;
    }
    // 错误类型
    return cell.getCellFormula();
  }

  /**
   * 根据类获取表头
   *
   * @param clazz 类
   * @param <T>   泛型
   * @return 表头list
   */
  private static <T> List<ExcelClassField> getExcelClassFieldList(final Class<T> clazz) {
    // 解析所有字段
    Field[] fields = clazz.getDeclaredFields();
    Map<Integer, List<ExcelClassField>> map = Maps.newHashMap();
    for (Field field : fields) {
      // 解析属性上的注解
      ExcelClassField excelClassField = getExcelClassField(field);
      // 按照顺序分组
      map.computeIfAbsent(excelClassField.getSort(), k -> Lists.newArrayList()).add(excelClassField);
    }
    // 对map重新进行排序
    Map<Integer, List<ExcelClassField>> sortMap = CollectionUtil.orderByKey(map);
    // 按照顺序追加进list
    return CollectionUtil.flatMapToList(sortMap.values());
  }

  /**
   * 获取属性上的注解
   *
   * @param field 属性
   * @return 属性上注解的解析
   */
  private static ExcelClassField getExcelClassField(final Field field) {
//    // 定义属性
//    ExcelClassField excelClassField = new ExcelClassField();
//    // 获取属性名
//    excelClassField.setFieldName(field.getName());
//    // 获取属性上的指定注解
//    ExcelExport annotation = field.getAnnotation(ExcelExport.class);
//    if (ObjectUtil.isEmpty(annotation)) {
//      // 等于null说明是没有添加注解的
//      excelClassField.setHasAnnotation(HasAnnotationEnum.UN_HAS);
//      excelClassField.setName(excelClassField.getFieldName());
//      excelClassField.setSort(0);
//      return excelClassField;
//    }
//    // 有 ExcelExport 注解情况
//    excelClassField.setHasAnnotation(HasAnnotationEnum.HAS);
//    // 设置自定义的名字
//    excelClassField.setName(annotation.value());
//    // 获取默认值
//    excelClassField.setDefaultValue(annotation.defaultValue());
//    // 设置排序顺序
//    excelClassField.setSort(annotation.sort());
//    return excelClassField;
    return null;
  }

  /**
   * 导出表格到本地
   *
   * @param <T>      导出数据类似，和K类型保持一致
   * @param filePath 文件父路径（如：D:/doc/excel/）
   * @param fileName 文件名称（不带尾缀，如：学生表）
   * @param list     导出数据
   */
  public static <T> File exportFile(final String filePath,
                                    final String fileName,
                                    final List<T> list) {
//    File file = FileUtil.getFile(filePath, fileName, XLSX);
//    ExcelExportInfo sheetData = getSheetData(list);
//    sheetData.setSheetName(fileName);
//    if (!FileUtil.judgeFileExists(file)) {
//      log.error("文件创建失败");
//      return null;
//    }
//    export(null, file, sheetData);
//    return file;
    return null;
  }

  /**
   * 导出表格到本地
   *
   * @param <T>      导出数据类似，和K类型保持一致
   * @param filePath 文件父路径（如：D:/doc/excel/）
   * @param fileName 文件名称（不带尾缀，如：学生表）
   * @param list     导出数据
   */
  public static <T> File exportFile(final String filePath,
                                    final String fileName,
                                    final List<T> list,
                                    final String suffix) {
//    File file = FileUtil.getFile(filePath, fileName, suffix);
//    ExcelExportInfo sheetData = getSheetData(list);
//    sheetData.setSheetName(fileName);
//    export(null, file, sheetData);
//    return file;
    return null;
  }

  /**
   * 导出sheet文件
   *
   * @param list 转为sheet数据集
   * @param <T>  转为sheet数据类型
   * @return 写入sheet的数据
   */
  private static <T> ExcelExportInfo getSheetData(final List<T> list) {
    if (ObjectUtil.isEmpty(list)) {
      return ExcelExportInfo.builder()
        .headSheetDataList(Lists.newArrayList()).sheetDataList(Lists.newArrayList()).build();
    }
    Class<?> clazz = list.get(0).getClass();
    // 获取表头字段
    List<ExcelClassField> excelClassFieldList = getExcelClassFieldList(clazz);
    Map<String, ExcelClassField> headFieldMap = CollectionUtil.toMap(excelClassFieldList,
      ExcelClassField::getFieldName, Function.identity());
    List<String> headFieldList = CollectionUtil.map(excelClassFieldList, ExcelClassField::getFieldName);

    ExcelExportInfo excelExportInfo = new ExcelExportInfo();
    // 设置表头
    excelExportInfo.setHeadSheetDataList(CollectionUtil.map(excelClassFieldList, ExcelClassField::getName));

    // 写到工作簿中的数据
    List<List<String>> sheetDataList = list.stream().map(t -> {
      Map<String, Object> fieldDataMap = getFieldDataMap(t);
      Set<String> fieldDataKeys = fieldDataMap.keySet();
      return headFieldList.stream().filter(fieldDataKeys::contains).map(headField -> {
        String value = StringUtil.getToString(fieldDataMap.get(headField));
        ExcelClassField excelClassField = headFieldMap.get(headField);
        // 判断是否有映射关系
        Map<String, String> kvMap = excelClassField.getKvMap();
        if (ObjectUtil.isEmpty(kvMap)) {
          return value;
        }
        return kvMap.get(value);
      }).collect(Collectors.toList());
    }).collect(Collectors.toList());
    excelExportInfo.setSheetDataList(sheetDataList);
    return excelExportInfo;
  }

  /**
   * 将对象转为Map<key: 字段名; value: 属性值>
   *
   * @param t   对象
   * @param <T> 泛型
   * @return Map<key: 字段名 ; value: 属性值>
   */
  private static <T> Map<String, Object> getFieldDataMap(final T t) {
    Map<String, Object> map = Maps.newHashMap();
    Field[] fields = t.getClass().getDeclaredFields();
    for (Field field : fields) {
      String fieldName = field.getName();
      map.put(fieldName, ClassUtils.getFieldValue(fieldName, t));
    }
    return map;
  }

  public static void export(final HttpServletResponse response,
                            final ExcelExportInfo sheetData) {
    export(response, null, sheetData);
  }

  /**
   * 设置表头背景色（灰色）
   *
   * @param cellStyle 列样式
   * @return 列样式
   */
  public static CellStyle createHeadCellStyle(final CellStyle cellStyle) {
    cellStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.index);
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
    return cellStyle;
  }

  /**
   * 设置表身背景色（默认色）
   *
   * @param cellStyle 列样式
   * @return 列样式
   */
  public static CellStyle createRowStyle(final CellStyle cellStyle) {
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    return cellStyle;
  }

  private static void export(final HttpServletResponse response, final File file,
                             final ExcelExportInfo sheetExportInfo) {
    try (SXSSFWorkbook book = createSXSSFWorkbook(sheetExportInfo)) {
      // 写数据
      if (ObjectUtil.notEmpty(response)) {
        // 前端导出
        write(response, book, sheetExportInfo.getSheetName());
      } else {
        // 本地导出
        try (FileOutputStream fos = new FileOutputStream(file)) {
          try (ByteArrayOutputStream ops = new ByteArrayOutputStream()) {
            book.write(ops);
            fos.write(ops.toByteArray());
          }
        }
      }
    } catch (IOException e) {
      log.error("写入文件异常: {}", e.getMessage(), e);
      throw ServerException.build(ErrorEnum.WRITE_FILE_ERROR);
    }
  }

  /**
   * 创建 Excel 表格
   *
   * @param sheetExportInfo 表格数据
   * @return Excel 表格
   */
  private SXSSFWorkbook createSXSSFWorkbook(final ExcelExportInfo sheetExportInfo) {
    // 整个 Excel 表格 book 对象
    SXSSFWorkbook book = new SXSSFWorkbook();
    CellStyle headStyle = createHeadCellStyle(book.createCellStyle());
    CellStyle rowStyle = createRowStyle(book.createCellStyle());
    // 每个 Sheet 页
    Sheet sheet = book.createSheet(sheetExportInfo.getSheetName());
    // 设置表格列宽度（默认为15个字节）
    sheet.setDefaultColumnWidth(15);
    // 创建合并算法数组
    List<List<String>> sheetDataList = sheetExportInfo.getSheetDataList();
    int rowLength = sheetDataList.size();
    // 设置表头
    setHeadSheet(sheet.createRow(0), headStyle, sheetExportInfo.getHeadSheetDataList());
    // 设置表身
    for (int i = 1; i < rowLength; i++) {
      // 每个 Sheet 页中的行数据
      Row row = sheet.createRow(i);
      List<String> rowList = sheetDataList.get(i);
      for (int j = 0; j < rowList.size(); j++) {
        addCell(row, rowStyle, rowList.get(j), j);
      }
    }
    return book;
  }

  /**
   * 设置表头
   *
   * @param headRow   首行表头
   * @param cellList  列对应的值
   * @param headStyle 表头样式
   */
  private void setHeadSheet(final Row headRow,
                            final CellStyle headStyle,
                            final List<String> cellList) {

    int cellSize = cellList.size();
    for (int j = 0; j < cellSize; j++) {
      addCell(headRow, headStyle, cellList.get(j), j);
    }
  }

  /**
   * 合并单元格
   *
   * @param sheet       要合并的工作簿
   * @param startRow    开始行
   * @param lastRow     结束行
   * @param startColumn 开始列
   * @param lastColumn  结束列
   */
  public static void mergeRange(final Sheet sheet,
                                final Integer startRow,
                                final Integer lastRow,
                                final Integer startColumn,
                                final Integer lastColumn) {
    CellRangeAddress cellRange = new CellRangeAddress(startRow, lastRow, startColumn, lastColumn);
    sheet.addMergedRegion(cellRange);
  }

  /**
   * 添加列
   *
   * @param row    哪一行
   * @param cs     样式
   * @param value  值
   * @param column 那一列
   */
  public static void addCell(final Row row, final CellStyle cs, final String value, final Integer column) {
    Cell cell = row.createCell(column);
    cell.setCellStyle(cs);
    cell.setCellValue(value);
  }

  private static void write(final HttpServletResponse response,
                            final SXSSFWorkbook book,
                            final String fileName) {
    try (ServletOutputStream out = response.getOutputStream()) {
      buildResponse(response, fileName);
      book.write(out);
      out.flush();
    } catch (IOException e) {
      log.error("写入文件异常: {}", e.getMessage(), e);
      throw ServerException.build(ErrorEnum.WRITE_FILE_ERROR);
    }
  }

  private static void buildResponse(final HttpServletResponse response,
                                    final String fileName) throws UnsupportedEncodingException {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    // 防止中文乱码
    String name = URLEncoder.encode(fileName, "UTF-8").replace("\\+", "%20") + XLSX;
    response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + name);
  }

  private static void setCellPicture(final SXSSFWorkbook wb, final Row sr,
                                     final Drawing<?> patriarch, final int x, final int y,
                                     final URL url) {
    // 设置图片宽高
    sr.setHeight((short) (IMG_WIDTH * IMG_HEIGHT));
    // （jdk1.7版本try中定义流可自动关闭）
    try (InputStream is = url.openStream(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
      byte[] buff = new byte[BYTES_DEFAULT_LENGTH];
      int rc;
      while ((rc = is.read(buff, 0, BYTES_DEFAULT_LENGTH)) > 0) {
        outputStream.write(buff, 0, rc);
      }
      // 设置图片位置
      XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, y, x, y + 1, x + 1);
      // 设置这个，图片会自动填满单元格的长宽
      anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);
      patriarch.createPicture(anchor, wb.addPicture(outputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  /**
   * 设置选中单元格的验证规则
   *
   * @param sheet     表格对象
   * @param selectMap 选中单元格的映射表，键为行号，值为选中单元格的列表
   */
  private static void setSelect(final Sheet sheet, final Map<Integer, List<String>> selectMap) {
    if (ObjectUtil.isEmpty(selectMap)) {
      return;
    }
    selectMap.forEach((y, list) -> {
      if (ObjectUtil.isEmpty(list)) {
        return;
      }
      String[] arr = list.toArray(new String[0]);
      DataValidationHelper helper = sheet.getDataValidationHelper();
      CellRangeAddressList addressList = new CellRangeAddressList(1, list.size(), y, y);
      DataValidationConstraint dvc = helper.createExplicitListConstraint(arr);
      DataValidation dv = helper.createValidation(dvc, addressList);
      if (dv instanceof HSSFDataValidation) {
        dv.setSuppressDropDownArrow(false);
      } else {
        dv.setSuppressDropDownArrow(true);
        dv.setShowErrorBox(true);
      }
      sheet.addValidationData(dv);
    });
  }

  public static String getStringValue(final Cell cell, final Boolean judgeLength) {
    if (cell == null) {
      return "";
    }
    try {
      String value = new BigDecimal(String.valueOf(cell)).stripTrailingZeros().toPlainString();
      if (Boolean.TRUE.equals(judgeLength) && value.length() != cell.getStringCellValue().length()) {
        return cell.getStringCellValue();
      } else {
        return value;
      }
    } catch (Exception e) {
      if (cell.getCellType() == CellType.ERROR) {
        return "";
      }
      return cell.getStringCellValue();
    }
  }
}
